package com.cwvs.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.cwvs.helper.UrlHelper;
import com.cwvs.utils.AppConstants;
import com.cwvs.utils.MySqlPageUtil;
import com.cwvs.utils.ObjectId;
import com.cwvs.utils.Pages;

/**
 * 需求DAO
 * 
 */
@Repository
public class AppNeedOrderDao {
	@Resource
	private JdbcTemplate jdbcTemplate;
	@Resource
	private MySqlPageUtil mySqlPageUtil;
	@Resource
	private UrlHelper urlHelper;

	/**
	 * 新增需求
	 * 
	 * @param m
	 * @return
	 */
	public String add(Map<String, Object> m) {
		StringBuffer sbf = new StringBuffer();
		sbf.append(" INSERT INTO APP_NEED_ORDER(");
		sbf.append("ID,TITLE,TOKEN,CTIME,UTIME,NEED_INFO,PIC_URL");
		sbf.append(" ,NEED_TIME,NEED_ADDRESS,PRICE,STATUS,STATUS_NAME,USERNAME)");
		sbf.append("VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)");
		List<Object> ls = new ArrayList<Object>();
		String id = ObjectId.getObjectId();
		ls.add(id);
		ls.add(MapUtils.getString(m, "TITLE"));
		ls.add(MapUtils.getString(m, "TOKEN"));
		ls.add(new Date());
		ls.add(new Date());
		ls.add(MapUtils.getString(m, "NEED_INFO"));
		ls.add(MapUtils.getString(m, "PIC_URL"));
		ls.add(MapUtils.getString(m, "NEED_TIME"));
		ls.add(MapUtils.getString(m, "NEED_ADDRESS"));
		ls.add(MapUtils.getDouble(m, "PRICE"));
		ls.add(MapUtils.getIntValue(m, "STATUS"));
		ls.add(MapUtils.getString(m, "STATUS_NAME"));
		ls.add(MapUtils.getString(m, "USERNAME"));
		if (jdbcTemplate.update(sbf.toString(), ls.toArray()) > 0) {
			return id;
		}
		return null;
	}

	/**
	 * 审核需求
	 * 
	 * @param m
	 * @return
	 */
	public boolean approve(Map<String, Object> m) {
		StringBuffer sbf = new StringBuffer();
		sbf.append(" UPDATE APP_NEED_ORDER SET ");
		sbf.append(" USERNAME=?,TITLE=? ,UTIME=?,NEED_INFO=?,NEED_TIME=? ");
		sbf.append(" ,NEED_ADDRESS=?,PRICE=?,STATUS=?,STATUS_NAME=? ");
		sbf.append(" WHERE ID=? ");
		List<Object> ls = new ArrayList<Object>();
		ls.add(MapUtils.getString(m, "USERNAME"));
		ls.add(MapUtils.getString(m, "TITLE"));
		ls.add(new Date());
		ls.add(MapUtils.getString(m, "NEED_INFO"));
		ls.add(MapUtils.getString(m, "NEED_TIME"));
		ls.add(MapUtils.getString(m, "NEED_ADDRESS"));
		ls.add(MapUtils.getDouble(m, "PRICE"));
		ls.add(MapUtils.getIntValue(m, "STATUS"));
		ls.add(MapUtils.getString(m, "STATUS_NAME"));
		ls.add(MapUtils.getString(m, "ID"));
		return jdbcTemplate.update(sbf.toString(), ls.toArray()) > 0;
	}

	/**
	 * 取消/完成需求
	 * 
	 * @param m
	 * @return
	 */
	public boolean cancelOrDone(Map<String, Object> m) {
		StringBuffer sbf = new StringBuffer();
		sbf.append(" UPDATE APP_NEED_ORDER SET ");
		sbf.append(" UTIME=?,STATUS=?,STATUS_NAME=? ");
		sbf.append(" WHERE ID=? ");
		List<Object> ls = new ArrayList<Object>();
		ls.add(new Date());
		ls.add(MapUtils.getIntValue(m, "STATUS"));
		ls.add(MapUtils.getString(m, "STATUS_NAME"));
		ls.add(MapUtils.getString(m, "ID"));
		return jdbcTemplate.update(sbf.toString(), ls.toArray()) > 0;
	}

	/**
	 * 通过ID获取需求信息
	 * 
	 * @param m
	 * @return
	 */
	public Map<String, Object> getInfoById(Map<String, Object> m) {
		StringBuffer sbf = new StringBuffer();
		sbf.append(" SELECT T1.ID,T1.TITLE,T1.CTIME,date_format(T1.CTIME,'%Y-%m-%d %T') C_TIME ");
		sbf.append(" ,T1.NEED_INFO,T1.PIC_URL,T1.NEED_TIME,T2.USER_ID");
		sbf.append(" ,T1.NEED_ADDRESS,T1.PRICE,T1.STATUS,T1.STATUS_NAME");
		sbf.append(" ,T3.USERNAME PHONE");
		sbf.append(" ,T1.USERNAME,T3.BIG_IMAGE HEADER_IMAGE FROM APP_NEED_ORDER T1 ");
		sbf.append(" LEFT JOIN APP_TOKEN T2 ON T1.TOKEN=T2.ID ");
		sbf.append(" LEFT JOIN APP_USER T3 ON T3.ID=T2.USER_ID ");
		sbf.append(" WHERE T1.ID=? ");
		List<Object> ls = new ArrayList<Object>();
		ls.add(MapUtils.getString(m, "ID"));
		List<Map<String, Object>> data = jdbcTemplate.queryForList(
				sbf.toString(), ls.toArray());
		if (!data.isEmpty()) {
			Map<String, Object> ob = data.get(0);
			if (StringUtils.isNotBlank(MapUtils.getString(ob, "PIC_URL"))) {
				ob.put("PIC_URL", urlHelper.img01UrlRootWithPort(MapUtils
						.getString(ob, "PIC_URL")));
			}
			if (StringUtils.isNotBlank(MapUtils.getString(ob, "HEADER_IMAGE"))) {
				ob.put("HEADER_IMAGE", urlHelper.img01UrlRootWithPort(MapUtils
						.getString(ob, "HEADER_IMAGE")));
			}
			return ob;
		}
		return null;
	}

	/**
	 * 判断用户是否已经接过此需求
	 * 
	 * @return
	 */
	public boolean isExists(Map<String, Object> m) {
		StringBuffer sbf = new StringBuffer();
		sbf.append(" SELECT COUNT(1) FROM APP_NEED_ACCEPT_ORDER T1 ");
		sbf.append(" LEFT JOIN APP_TOKEN T2 ON T2.ID=T1.TOKEN ");
		sbf.append(" WHERE T1.ID=? AND T2.USER_ID=? ");
		List<Object> params = new ArrayList<Object>();
		params.add(MapUtils.getString(m, "ID"));
		params.add(MapUtils.getString(m, "USER_ID"));
		return jdbcTemplate.queryForInt(sbf.toString(), params.toArray()) > 0;
	}

	/**
	 * 保存揭榜信息
	 */
	public boolean saveAcceptInfo(Map<String, Object> m) {
		StringBuffer sbf = new StringBuffer();
		sbf.append(" INSERT INTO APP_NEED_ACCEPT_ORDER (");
		sbf.append(" ID,TOKEN,NAME,PHONE,REMARK,NEED_ID");
		sbf.append(" ,CTIME,UTIME,STATUS,STATUS_NAME)");
		sbf.append(" VALUES(?,?,?,?,?,?,?,?,?,?)");
		List<Object> params = new ArrayList<Object>();
		params.add(ObjectId.getObjectId());
		params.add(MapUtils.getString(m, "TOKEN"));
		params.add(MapUtils.getString(m, "NAME"));
		params.add(MapUtils.getString(m, "PHONE"));
		params.add(MapUtils.getString(m, "REMARK"));
		params.add(MapUtils.getString(m, "ID"));
		params.add(new Date());
		params.add(new Date());
		params.add(MapUtils.getIntValue(m, "STATUS"));
		params.add(MapUtils.getString(m, "STATUS_NAME"));
		return jdbcTemplate.update(sbf.toString(), params.toArray()) > 0;
	}

	/**
	 * 需求列表
	 * 
	 * @return
	 */
	public Pages needList(Map<String, Object> m, Pages page) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT T1.ID,T1.TITLE,T1.NEED_INFO,T1.PIC_URL,T1.UTIME,T1.CTIME ");
		sql.append(" ,T1.NEED_TIME,T1.NEED_ADDRESS,T1.PRICE,T1.STATUS,T1.STATUS_NAME ");
		sql.append(" ,DATE_FORMAT(T1.UTIME,'%Y-%m-%d %T') U_TIME,T3.BIG_IMAGE HEADER_IMAGE ");
		sql.append(" ,T1.USERNAME,T3.USERNAME USER_NAME ");
		sql.append(" FROM APP_NEED_ORDER T1 ");
		sql.append(" LEFT JOIN APP_TOKEN T2 ON T2.ID=T1.TOKEN ");
		sql.append(" LEFT JOIN APP_USER T3 ON T3.ID=T2.USER_ID ");
		sql.append(" WHERE 1=1  ");
		List<Object> params = new ArrayList<Object>();
		if (StringUtils.isNotBlank(MapUtils.getString(m, "STATUS"))) {
			String[] status = MapUtils.getString(m, "STATUS").split(",");
			sql.append(" AND (1=2 ");
			for (String s : status) {
				sql.append(" OR T1.STATUS=? ");
				params.add(Integer.parseInt(s));
			}
			sql.append(")");
		}

		if (StringUtils.isNotBlank(MapUtils.getString(m, "USER_ID"))) {
			sql.append(" AND T3.ID=? ");
			params.add(MapUtils.getString(m, "USER_ID"));
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "TITLE"))) {
			sql.append(" AND UPPER(T1.TITLE) LIKE ? ");
			params.add("%" + MapUtils.getString(m, "TITLE").toUpperCase() + "%");
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "USER_NAME"))) {
			sql.append(" AND UPPER(T3.USERNAME) LIKE ? ");
			params.add("%" + MapUtils.getString(m, "USER_NAME").toUpperCase()
					+ "%");
		}
		sql.append(" ORDER BY T1.UTIME DESC ");
		String page_sql = mySqlPageUtil.getPageSql(page, sql.toString());
		List<Map<String, Object>> ls = jdbcTemplate.queryForList(page_sql,
				params.toArray());
		for (Map<String, Object> map : ls) {
			if (StringUtils.isNotBlank(MapUtils.getString(map, "PIC_URL"))) {
				map.put("PIC_URL", urlHelper.img01UrlRootWithPort(MapUtils
						.getString(map, "PIC_URL")));
			}
			if (StringUtils.isNotBlank(MapUtils.getString(map, "HEADER_IMAGE"))) {
				map.put("HEADER_IMAGE", urlHelper.img01UrlRootWithPort(MapUtils
						.getString(map, "HEADER_IMAGE")));
			}
			if (StringUtils.isBlank(MapUtils.getString(m, "IS_SHOW"))) {
				map.remove("USER_NAME");
			}
		}
		page.setPage(ls);
		page.setTotalRecord(needCount(m));
		return page;
	}

	private int needCount(Map<String, Object> m) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1) FROM APP_NEED_ORDER T1  ");
		sql.append(" LEFT JOIN APP_TOKEN T2 ON T2.ID=T1.TOKEN ");
		sql.append(" LEFT JOIN APP_USER T3 ON T3.ID=T2.USER_ID ");
		sql.append(" WHERE 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if (StringUtils.isNotBlank(MapUtils.getString(m, "STATUS"))) {
			String[] status = MapUtils.getString(m, "STATUS").split(",");
			sql.append(" AND (1=2 ");
			for (String s : status) {
				sql.append(" OR T1.STATUS=? ");
				params.add(Integer.parseInt(s));
			}
			sql.append(")");
		}

		if (StringUtils.isNotBlank(MapUtils.getString(m, "USER_ID"))) {
			sql.append(" AND T3.ID=? ");
			params.add(MapUtils.getString(m, "USER_ID"));
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "TITLE"))) {
			sql.append(" AND UPPER(T1.TITLE) LIKE ? ");
			params.add("%" + MapUtils.getString(m, "TITLE").toUpperCase() + "%");
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "USER_NAME"))) {
			sql.append(" AND UPPER(T3.USERNAME) LIKE ? ");
			params.add("%" + MapUtils.getString(m, "USER_NAME").toUpperCase()
					+ "%");
		}
		return jdbcTemplate.queryForInt(sql.toString(), params.toArray());
	}

	/**
	 * 揭榜列表
	 * 
	 * @return
	 */
	public Pages acceptList(Map<String, Object> m, Pages page) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT T1.ID,T2.TITLE,T1.NEED_ID,T1.UTIME,T1.CTIME,T1.STATUS ");
		sql.append(" ,DATE_FORMAT(T1.UTIME,'%Y-%m-%d %T') U_TIME,T1.STATUS_NAME");
		sql.append(" ,T1.REMARK ,T1.NAME,T1.PHONE,T2.PRICE ");
		sql.append(" FROM APP_NEED_ACCEPT_ORDER T1 ");
		sql.append(" LEFT JOIN APP_NEED_ORDER T2 ON T1.NEED_ID=T2.ID ");
		sql.append(" LEFT JOIN APP_TOKEN T3 ON T3.ID=T1.TOKEN ");
		sql.append(" LEFT JOIN APP_USER T4 ON T4.ID=T3.USER_ID ");
		sql.append(" WHERE 1=1  ");
		List<Object> params = new ArrayList<Object>();
		if (StringUtils.isNotBlank(MapUtils.getString(m, "STATUS"))) {
			String[] status = MapUtils.getString(m, "STATUS").split(",");
			sql.append(" AND (1=2 ");
			for (String s : status) {
				sql.append(" OR T1.STATUS=? ");
				params.add(Integer.parseInt(s));
			}
			sql.append(")");
		}

		if (StringUtils.isNotBlank(MapUtils.getString(m, "USER_ID"))) {
			sql.append(" AND T4.ID=? ");
			params.add(MapUtils.getString(m, "USER_ID"));
		}

		if (StringUtils.isNotBlank(MapUtils.getString(m, "NEED_ID"))) {
			sql.append(" AND T1.NEED_ID=? ");
			params.add(MapUtils.getString(m, "NEED_ID"));
		}
		sql.append(" ORDER BY T1.UTIME DESC ");
		if (StringUtils.isNotBlank(MapUtils.getString(m, "NEED_ID"))) {
			sql.append(" ,T1.STATUS ASC ");
		}
		String page_sql = mySqlPageUtil.getPageSql(page, sql.toString());
		List<Map<String, Object>> ls = jdbcTemplate.queryForList(page_sql,
				params.toArray());
		page.setPage(ls);
		page.setTotalRecord(acceptCount(m));
		return page;
	}

	private int acceptCount(Map<String, Object> m) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1) FROM APP_NEED_ACCEPT_ORDER T1  ");
		sql.append(" LEFT JOIN APP_TOKEN T2 ON T2.ID=T1.TOKEN ");
		sql.append(" LEFT JOIN APP_USER T3 ON T3.ID=T2.USER_ID ");
		sql.append(" WHERE 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if (StringUtils.isNotBlank(MapUtils.getString(m, "STATUS"))) {
			String[] status = MapUtils.getString(m, "STATUS").split(",");
			sql.append(" AND (1=2 ");
			for (String s : status) {
				sql.append(" OR T1.STATUS=? ");
				params.add(Integer.parseInt(s));
			}
			sql.append(")");
		}

		if (StringUtils.isNotBlank(MapUtils.getString(m, "USER_ID"))) {
			sql.append(" AND T3.ID=? ");
			params.add(MapUtils.getString(m, "USER_ID"));
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "NEED_ID"))) {
			sql.append(" AND T1.NEED_ID=? ");
			params.add(MapUtils.getString(m, "NEED_ID"));
		}
		return jdbcTemplate.queryForInt(sql.toString(), params.toArray());
	}

	/**
	 * 回写订单支付charge
	 */
	public void updateOrderCharge(String orderId, String chargeId) {
		StringBuffer sbf = new StringBuffer();
		sbf.append("UPDATE APP_NEED_ORDER SET CHARGE_ID=? WHERE ID=? ");
		jdbcTemplate.update(sbf.toString(), new Object[] { chargeId, orderId });
	}

	/**
	 * 通过ID获取支付CHARGE_ID
	 * 
	 * @param m
	 * @return
	 */
	public Map<String, Object> getChargeById(Map<String, Object> m) {
		StringBuffer sbf = new StringBuffer();
		sbf.append(" SELECT T1.ID,T1.TITLE,T1.CTIME,date_format(T1.CTIME,'%Y-%m-%d %T') C_TIME ");
		sbf.append(" ,T1.NEED_INFO,T1.PIC_URL,T1.NEED_TIME,T1.CHARGE_ID ");
		sbf.append(" ,T1.NEED_ADDRESS,T1.PRICE,T1.STATUS,T1.STATUS_NAME");
		sbf.append(" ,T1.USERNAME FROM APP_NEED_ORDER T1 ");
		sbf.append(" WHERE T1.ID=? ");
		List<Object> ls = new ArrayList<Object>();
		ls.add(MapUtils.getString(m, "ID"));
		List<Map<String, Object>> data = jdbcTemplate.queryForList(
				sbf.toString(), ls.toArray());
		if (!data.isEmpty()) {
			Map<String, Object> ob = data.get(0);
			if (StringUtils.isNotBlank(MapUtils.getString(ob, "PIC_URL"))) {
				ob.put("PIC_URL", urlHelper.img01UrlRootWithPort(MapUtils
						.getString(ob, "PIC_URL")));
			}
			return ob;
		}
		return null;
	}

	/**
	 * 揭榜
	 */
	public void updateAcceptStatus(Map<String, Object> m) {
		StringBuffer sbf = new StringBuffer();
		sbf.append(" UPDATE APP_NEED_ACCEPT_ORDER SET STATUS=? ,STATUS_NAME=? ");
		sbf.append(" WHERE NEED_ID=? ");
		jdbcTemplate.update(
				sbf.toString(),
				new Object[] { AppConstants.STATUS_CANCEL, "揭榜失败",
						MapUtils.getString(m, "NEED_ID") });
		if (StringUtils.isNotBlank(MapUtils.getString(m, "ID"))) {
			StringBuffer st = new StringBuffer();
			st.append(" UPDATE APP_NEED_ACCEPT_ORDER SET STATUS=? ,STATUS_NAME=? ");
			st.append(" WHERE NEED_ID=? AND ID=?  ");
			jdbcTemplate.update(
					st.toString(),
					new Object[] { AppConstants.STATUS_DONE, "揭榜成功",
							MapUtils.getString(m, "NEED_ID"),
							MapUtils.getString(m, "ID") });
		}
	}

	/**
	 * 通过ID获取揭榜信息
	 */
	public Map<String, Object> getAcceptOrderById(Map<String, Object> m) {
		StringBuffer sbf = new StringBuffer();
		sbf.append("SELECT T1.ID,T1.PHONE ");
		sbf.append(" FROM APP_NEED_ACCEPT_ORDER T1 ");
		sbf.append(" WHERE T1.ID=? ");
		List<Map<String, Object>> ls = jdbcTemplate.queryForList(
				sbf.toString(), new Object[] { MapUtils.getString(m, "ID") });
		if (ls.isEmpty()) {
			return null;
		}
		return ls.get(0);
	}
}
